// ### 作业：建一个用户表（有姓名,年龄,性别,创建时间,修改时间）,使用模块加mysql模块,实现对数据的增删改查
// ###       修改时记得，把修改时间改成修改时的时间，能实现分页（选做）


let mysql=require('mysql');
const { createConnection } = require('net');
let conn=mysql.createConnection({host:'localhost',user:'root',password:'root',database:'admin'});
conn.connect();


//查询
let sql="select * from admin"
conn.query(sql,(err,data)=>{
    console.log(err);
    console.log(data);
})



//新增
let createTime=new Date().toLocaleString().replace('/','-').replace('/','-');
//console.log(createTime);
let addData="insert into admin (`name`,`age`,`sex`,`position`,`salary`,`createTime`,`updateTime`) value ('asd','20','男','前端工程师','25000','"+createTime+"','"+createTime+"')";
conn.query(addData,(err,data)=>{
    console.log(err);
    console.log(data);
});


//修改
let updateTime=new Date().toLocaleString().replace('/','-').replace('/','-');
let updateData="update admin set name='guyguygu',updateTime='"+updateTime+"' where id=6";
conn.query(updateData,(err,data)=>{
    console.log(err);
    console.log(data);
});


//删除
let deleteData="DELETE FROM admin where id=7;";
conn.query(deleteData,(err,data)=>{
    console.log(err);
    console.log(data);
});

//分页
let page=2;
let pageSize=2;
let pageSql="select * from admin limit "+(page-1)*pageSize+","+pageSize;
conn.query(pageSql,(err,data)=>{
    console.log(err);
    console.log(data);
});

// ###       实现分页,能实现男女个数和总数的统计,能统计出（小于18） 和 18-45 和 45岁以上的人数

conn.query('SELECT count(*),sex FROM admin GROUP BY sex',(err,data)=>{
    console.log(err);
    console.log(data);
});

let one="SELECT count(*) as '小于18的人数' FROM admin where age<18";
let two="SELECT count(*) as '18-45的人数' FROM admin where age BETWEEN 18 and 45";
let three="SELECT count(*) as '大于45的人数' FROM admin where age>45";
conn.query(one,(err,data)=>{
    console.log(err);
    console.log(data);
});
conn.query(two,(err,data)=>{
    console.log(err);
    console.log(data);
});
conn.query(three,(err,data)=>{
    console.log(err);
    console.log(data);
});

conn.end();